package com.haisen.poi.basic_poi;

import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.util.Date;

import javafx.scene.effect.ColorInput;
import javafx.util.converter.TimeStringConverter;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.time.DateFormatUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.openxml4j.exceptions.OpenXML4JException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.model.SharedStringsTable;
import org.apache.poi.xssf.model.StylesTable;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.tools.ant.taskdefs.Available;
import org.xml.sax.Attributes;
import org.xml.sax.ContentHandler;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
import org.xml.sax.XMLReader;
import org.xml.sax.helpers.DefaultHandler;
import org.xml.sax.helpers.XMLReaderFactory;

@Slf4j
public abstract class BigExcelReader{
	/**
	 * 单元格中的数据可能的数据类型
	 */
	enum xssfDataType {
		BOOLEN,ERROR,FORMULA, INLINESTR, SSTINDEX, NUMBER,DATE,NULL
	}
	public static final String DATE_FORMAT_STR = "yyyy-MM-dd HH:mm:ss";

	private InputStream sheet;
	private XMLReader parser;
	private InputSource sheetSource;
	private int index = 0;
	
	/**
	 * 读大数据量Excel
	 * 
	 * @param filename 文件名
	 * @param
	 * @throws IOException
	 * @throws OpenXML4JException
	 * @throws SAXException
	 */
	public void excelReaderByPath(String filename) throws IOException, OpenXML4JException, SAXException{
		OPCPackage pkg = OPCPackage.open(filename);
		//System.out.println(pkg.toString());
		init(pkg);
	}
	
	/**
	 * 读大数据量Excel
	 * 
	 * @param file Excel文件
	 *
	 * @throws IOException
	 * @throws OpenXML4JException
	 * @throws SAXException
	 */
	public void excelReaderByFile(File file) throws IOException, OpenXML4JException, SAXException{
		OPCPackage pkg = OPCPackage.open(file);
		init(pkg);
	}
	
	/**
	 * 读大数据量Excel
	 * 
	 * @param in Excel文件输入流
	 * @throws IOException
	 * @throws OpenXML4JException
	 * @throws SAXException
	 */
	public void excelReaderByStream(InputStream in) throws IOException, OpenXML4JException, SAXException{
		OPCPackage pkg = OPCPackage.open(in);
		init(pkg);
	}
	
	/**
	 * 初始化 将Excel转换为XML
	 * 
	 * @param pkg
	 * @throws IOException
	 * @throws OpenXML4JException
	 * @throws SAXException
	 */
	private void init(OPCPackage pkg) throws IOException, OpenXML4JException, SAXException{
		XSSFReader xssfReader = new XSSFReader(pkg);
		SharedStringsTable sharedStringsTable = xssfReader.getSharedStringsTable();
		StylesTable stylesTable = xssfReader.getStylesTable();
		sheet = xssfReader.getSheet("rId1");
		parser = fetchSheetParser(sharedStringsTable, stylesTable);
		sheetSource = new InputSource(sheet);
		//循环批量导入excel时,否则不同标题excel批量插入时，列写入错误
		index =0;

	}
	
	/**
	 * 执行解析操作
	 * 
	 * @return 读取的Excel行数
	 */
	public int parse(){
		try {
			parser.parse(sheetSource);
		} 
		catch (IOException e) {
			e.printStackTrace();
		} 
		catch (SAXException e) {
			e.printStackTrace();
		}
		finally{
			if(sheet != null){
				try {
					sheet.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}
		return index;
	}
	
	private XMLReader fetchSheetParser(SharedStringsTable sharedStringsTable, StylesTable stylesTable) throws SAXException {
		//单元格格式解析不出来
		//XMLReader parser = XMLReaderFactory.createXMLReader();
		XMLReader parser = 	XMLReaderFactory.createXMLReader(
				"org.apache.xerces.parsers.SAXParser"
			);
		 
		ContentHandler handler = new SheetHandler(sharedStringsTable, stylesTable);
		parser.setContentHandler(handler);
		return parser;
	}
	
	/**
	 * SAX解析的处理类
	 * 每解析一行数据后通过outputRow(String[] datas, int[] rowTypes, int rowIndex)方法进行输出
	 * 
	 * @author zpin
	 */
	private class SheetHandler extends DefaultHandler {

		private SharedStringsTable sharedStringsTable; // 存放映射字符串
		private StylesTable stylesTable;// 存放单元格样式
		private String readValue;// 存放读取值
		private xssfDataType dataType;// 定义枚举类型来存储单元格类型
		private String[] rowDatas;// 存放一行中的所有数据
		private String[] rowTypes;// 存放一行中所有数据类型
		private int colIdx;// 当前所在列

		private DataFormatter formatter = new DataFormatter();
		private short formatIndex = 0;
     	private String formatString = null;// 对数值型的数据直接读为数值，不对其格式化，所以隐掉此处
		
		private SheetHandler(SharedStringsTable sst,StylesTable stylesTable) {
			this.sharedStringsTable = sst;
			this.stylesTable = stylesTable;
		}
		
		public void startElement(String uri, String localName, String name,
				Attributes attributes) throws SAXException {
			if("c".equals(name)) {// c > 单元格
				colIdx = getColumn(attributes);
				//cellIType=Null，此时都是s类型,赋值给了dateType
				/**
				 * 查看excel的xml格式，将excel.xlsx命名为*.rar可以查看
				 *<row r="2" spans="1:7">
				 <c r="A2"><v>1</v></c>
				 <c r="B2" s="2" t="s"><v>0</v></c>--索引 文字有限，字符型设计成索引查找，t =s ;
				 <c r="C2" s="2" t="s"><v>1</v></c>--索引
				 <c r="D2"><v>13800138000</v></c> --无t 数字类型，但wu s，字符型数字,当成数字也行？
				 <c r="E2" s="4"><v>30</v></c>
				 <c r="F2" s="3"><v>1.21</v></c>--
				 <c r="G2" s="1"><v>42938</v></c>---有s属性，再区分数字和日期
				 </row>
				 */
				//如果非字符串类型，则cellType=Null;
				String cellType = attributes.getValue("t");
				String cellStyle = attributes.getValue("s");
				//log.info("type={},style={}",cellType,cellStyle);
				//cellType默认设置为数字,没有"t","s" 属性
				this.dataType = xssfDataType.NUMBER;
				this.formatIndex = -1 ;
				//数字时，日期标识符
				this.formatString = null;
				if (cellType != null && "b".equals(cellType)){
					this.dataType = xssfDataType.BOOLEN;
				}
		        else if (cellType != null && "e".equals(cellType)){
		        	this.dataType = xssfDataType.ERROR;
		        }
		        else if (cellType != null && "inlineStr".equals(cellType)){
		        	this.dataType = xssfDataType.INLINESTR;
		        }
		        else if (cellType != null && "s".equals(cellType)){
		        	this.dataType = xssfDataType.SSTINDEX;
		        }
		        else if (cellType != null && "str".equals(cellType)){
		        	this.dataType = xssfDataType.FORMULA;
		        } else if(cellStyle != null){
		        	//不匹配条件默认为数字，数字都有s属性，再根据formIndex值判断出日期类型
		        	int styleIndex = Integer.parseInt(cellStyle);
		            XSSFCellStyle style = stylesTable.getStyleAt(styleIndex);
		            this.formatIndex = style.getDataFormat();
		            //此处不能复制，end解析时，需要根据formatString == null 来判断是否数字
		             this.formatString = style.getDataFormatString();
					//log.info("日期格式 formatIndex={},formatString={}",style.getDataFormat(),style.getDataFormatString());
                    short format = this.formatIndex;
                    //通过formatIndex 判断出为日期类型,每个表格不成，不成立
					if ("m/d/yy".equals(formatString) || "yyyy/m/d;@".equals(formatString) || "mm/dd/yy;@".equals(formatString) )
					{ // 日期
						this.formatString = "yyyy-MM-dd";
						this.dataType = xssfDataType.DATE;
					} else if ("yyyy/m/d\\ h:mm\\ AM/PM;@".equals(formatString) || "yyyy/m/d\\ h:mm;@" .equals(formatString)){
						this.formatString = "yyyy-MM-dd HH:mm:ss";
						this.dataType = xssfDataType.DATE;
					}
		        }
			}
			// 解析到一行的开始处时，初始化数组
			else if(name.equals("row")){
				int cols = getColsNum(attributes);// 获取该行的单元格数
				rowDatas = new String[cols];
				rowTypes = new String[cols];
			}
			readValue = "";
		}
		
		public void endElement(String uri, String localName, String name)
				throws SAXException {
			if(name.equals("v")) { // 单元格的值
				//log.info("单元格解析：第{}列，类型={},值={}", colIdx,dataType,readValue);
				switch(this.dataType.name()){
					case "BOOLEAN": {
						char first = readValue.charAt(0);
						rowDatas[colIdx] = first == '0' ? "FALSE" : "TRUE";
						rowTypes[colIdx] = "BOOLEAN";
						break;
					}
					case "ERROR": {
						rowDatas[colIdx] = "ERROR:" + readValue.toString();
						rowTypes[colIdx] = "STRING";
						break;
					}
					case "INLINESTR": {
						rowDatas[colIdx] = new XSSFRichTextString(readValue).toString();
						rowTypes[colIdx] = "STRING";
						break;
					}
					case "SSTINDEX":{
						String sstIndex = readValue.toString();
						try {
							int idx = Integer.parseInt(sstIndex);
							XSSFRichTextString rtss = new XSSFRichTextString(sharedStringsTable.getEntryAt(idx));
							rowDatas[colIdx] = rtss.toString();
							rtss = null;
						} catch (NumberFormatException ex) {
							rowDatas[colIdx] = readValue.toString();
						}
						rowTypes[colIdx] = "STRING";
						break;
					}
					case "FORMULA":{
						rowDatas[colIdx] = readValue;
						rowTypes[colIdx] = "STRING";
						break;
					}
					case "NUMBER":{
						if (formatString != null) {
							//数字的格式化：日期格式 formatIndex=177,formatString=0.00_
							rowDatas[colIdx] = formatter.formatRawCellContents(Double.parseDouble(readValue), formatIndex, formatString).trim();
						} else {
							rowDatas[colIdx] = readValue;
						}
						 rowDatas[colIdx] = rowDatas[colIdx].replace("_", "").trim();
						//判断数字类型，设置数据表个字段类型
						int dotIndex = rowDatas[colIdx].indexOf(".");
						if ( dotIndex > 0){
                            int scale = rowDatas[colIdx].length() - dotIndex -1;
                            //转double位数太长了，截取精度为2
                            scale = scale >2 ? 2 : scale ;
							rowTypes[colIdx] = "DECIMAL(10, "+scale+")";
						}else
							rowTypes[colIdx] = "INT";
						  break;
						}
					case "DATE": {
						   String n = readValue.toString();
						//日期类型，到1900的相差天数
						if (formatString != null) {
							rowDatas[colIdx] = formatter.formatRawCellContents(Double.parseDouble(readValue), formatIndex, formatString).trim();
						} else {
							Double d = Double.parseDouble(n);
							Date date = HSSFDateUtil.getJavaDate(d);
							rowDatas[colIdx] = DateFormatUtils.format(date, DATE_FORMAT_STR );
						}
						rowTypes[colIdx] = "TIMESTAMP";
						break;
					}
					default:
						rowDatas[colIdx] = " ";
						rowTypes[colIdx] ="STRING";
				    }
				}
			// 当解析的一行的末尾时，输出数组中的数据
			else if(name.equals("row")){
				outputRow(rowDatas, rowTypes, index++);
			}
		}

		public void characters(char[] ch, int start, int length)
				throws SAXException {
			readValue += new String(ch, start, length);
		}
	}
	
	/**
	 * 输出每一行的数据
	 * 
	 * @param datas 数据
	 * @param rowTypes 数据类型
	 * @param rowIndex 所在行
	 */
	protected abstract void outputRow(String[] datas, String[] rowTypes, int rowIndex);

	/**取出当前列
	 *<c r="A2"><v>1</v></c>
	 * @param attrubuts
	 * @return
	 */
	private int getColumn(Attributes attrubuts) {  
		String name = attrubuts.getValue("r");
        int column = -1;
        for (int i = 0; i < name.length(); ++i) {
        	if (Character.isDigit(name.charAt(i))) {
				break;
			}
            int c = name.charAt(i);  
            column = (column + 1) * 26 + c - 'A';
        }
        return column;  
    }

	/**取出列
	 * <row r="2" spans="1:7">取出7来，列数,总共有几列
	 */
	private int getColsNum(Attributes attrubuts){
		String spans = attrubuts.getValue("spans");
		String cols = spans.substring(spans.indexOf(":") + 1);
		return Integer.parseInt(cols);
	}
}
